﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DotNet.JCore.Entity;
using DotNet.Collections;
using DotNet.Extensions;
using DotNet.Utility;
using DotNet.Helper;
using DotNet.Data;

namespace DotNet.JCore.Service
{
    public class SyncCarExitStationService
    {
        private JCoreRepository<SyncCarExitStation> repos = new JCoreRepository<SyncCarExitStation>();
        private JCoreRepository<SyncCarExitStationModel> repository = new JCoreRepository<SyncCarExitStationModel>();
        private JCoreRepository<SystemReg> reposys = new JCoreRepository<SystemReg>();
        private JCoreRepository<TicketCheckSum> respon = new JCoreRepository<TicketCheckSum>();

        /// <summary>
        /// 获取对象
        /// </summary>
        /// <param name = "id" > 主键 </ param >
        public SyncCarExitStationModel Get(string id)
        {
            var SaftSQL = @"SELECT ID,BusCode, OutTime,OwnerStation ,OwnerStationName,SaleCount,SeatCount, OutNum, Driver1Name, Driver1Tel,
                            LineName,OwnerCompName,RunStatus,Syn_Time,CheckStatus,PointName,PointId,DATEDIFF(MI,OutTime,GETDATE()) AS RunSecond
                        FROM  V_ToCheckData 
                        WHERE CheckStatus=0 AND ID=@id";
            return repos.Database.Query<SyncCarExitStationModel>(SaftSQL, new { id }).FirstOrDefault();
        }

        /// <summary>
        /// 获取对象
        /// </summary>
        /// <param name="userId">账号主键</param>
        public SyncCarExitStation GetBySystemRegId(string Id)
        {
            return repos.Get(p => p.Id == Id);
        }

        /// <summary>
        /// 获取启用的简单对象集合(已排序)
        /// </summary>
        public List<Simple> GetSimpleList()
        {
            var query = repos.SQL
                .OrderByDesc(p => p.OutTime)
                .Select(p => p.Id, p => p.BusCode);

            return repos.Fetch(query).
                Select(p => new Simple(p.Id, p.BusCode, SpellHelper.GetSpell(p.BusCode)))
                .ToList();
        }

        /// <summary>
        /// 获取启用的对象集合(导出)
        /// </summary>
        /// <param name="name">车牌号</param>
        /// <param name="Point">检查点</param>
        /// <param name="Station">所属客运站</param>
        /// <param name="startDate">起始日期</param>
        /// <param name="endDate">截止日期</param>
        /// <returns></returns>
        public List<SyncCarExitStation> GetSyncCarExitStationList(string name, string Point, string Station, DateTime? startDate, DateTime? endDate)
        {
            var sql = @"SELECT ID,BusCode, OutTime,OwnerStation ,OwnerStationName,SaleCount,SeatCount, OutNum, Driver1Name, Driver1Tel,
                            LineName,OwnerCompName,RunStatus,Syn_Time,CheckStatus,PointName,PointId,DATEDIFF(MI,OutTime,GETDATE()) AS RunSecond
                        FROM  V_ToCheckData 
                        WHERE CheckStatus=0";
            string str_where = " ";
            if (!startDate.HasValue && !endDate.HasValue)
            {
                str_where += " AND OutTime > CAST(GETDATE() as date)";
            }
            else
            {
                if (startDate.HasValue)
                {
                    startDate = startDate.ToDateTime();
                    str_where += " AND OutTime > @StartDate";
                }
                if (endDate.HasValue)
                {
                    endDate = endDate.ToDateTime().AddDays(1);
                    str_where += " AND OutTime < @EndDate";
                }
            }
            if (name.IsNotEmpty() && !ValidateHelper.IsStringRegExMatch(name, true, "[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]"))
            {
                str_where += " AND BusCode LIKE '%" + name + "%'";
            }
            if (Point.IsNotEmpty())
            {
                str_where += " AND PointId=@PointId";
            }
            if (Station.IsNotEmpty())
            {
                str_where += " AND OwnerStation=@OwnerStation";
            }
            str_where += " ORDER BY OutTime ASC";
            return repos.Database.Query<SyncCarExitStation>(sql + str_where, new { StartDate = startDate, EndDate = endDate, PointId = Point, OwnerStation = Station }).ToList();
        }

        /// <summary>
        /// 获取启用的对象集合(已排序)
        /// </summary>
        /// <param name="name">名称关键字</param>
        /// <param name="enabled">有限</param>
        /// <param name="pointId">检查点</param>
        public List<SyncCarExitStation> GetList(string name, string Point, string Station, DateTime? startDate, DateTime? endDate)
        {
            var query = repos.SQL.OrderByDesc(p => p.Syn_Time);
            SetQuery(query, name, Point, Station, startDate, endDate);
            return repos.Fetch(query);
        }

        /// <summary>
        /// 获取对象分页集合
        /// </summary>
        /// <param name="pageCondition">分页对象</param>
        /// <param name="name">名称关键字</param>
        /// <param name="enabled">有限</param>
        /// <param name="pointId">检查点</param>
        public PageList<SyncCarExitStation> GetPageList(PaginationCondition pageCondition,
            string name, string Point, string Station, DateTime? startDate, DateTime? endDate)
        {
            var sql = @"SELECT ID,BusCode, OutTime,OwnerStation ,OwnerStationName,SaleCount,SeatCount, OutNum, Driver1Name, Driver1Tel,
                            LineName,OwnerCompName,RunStatus,Syn_Time,CheckStatus,PointName,PointId,DATEDIFF(MI,OutTime,GETDATE()) AS RunSecond
                        FROM  V_ToCheckData 
                        WHERE CheckStatus=0";
            string str_where = " ";
            if (!startDate.HasValue && !endDate.HasValue)
            {
                str_where += " AND OutTime > CAST(GETDATE() as date)";
            }
            else
            {
                if (startDate.HasValue)
                {
                    startDate = startDate.ToDateTime();
                    str_where += " AND OutTime > @StartDate";
                }
                if (endDate.HasValue)
                {
                    str_where += " AND OutTime < @EndDate";
                }
            }
            if (name.IsNotEmpty() && !ValidateHelper.IsStringRegExMatch(name, true, "[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]"))
            {
                str_where += " AND BusCode LIKE '%" + name + "%'";
            }
            if (Point.IsNotEmpty())
            {
                str_where += " AND PointId=@PointId";
            }
            if (Station.IsNotEmpty())
            {
                str_where += " AND OwnerStation=@OwnerStation";
            }
            if (pageCondition.OrderName.IsNotEmpty())
            {
                str_where += " ORDER BY " + pageCondition.OrderName + " " + pageCondition.OrderDir;
            }
            else
            {
                str_where += " ORDER BY OutTime ASC";
            }
            var query = repos.Page(pageCondition.PageIndex, pageCondition.PageSize, sql + str_where, new { StartDate = startDate, EndDate = endDate, PointId = Point, OwnerStation = Station });
            return query;
        }

        /// <summary>
        /// 设置查询条件
        /// </summary>
        /// <param name="query">查询对象</param>
        /// <param name="search">数据对象</param>
        public void SetQuery(SQLQuery<SyncCarExitStation> query, string name, string Point, string Station, DateTime? startDate, DateTime? endDate)
        {
            if (name.IsNotEmpty())
            {
                name = name.Trim();
                query.Where(p => p.BusCode.Contains(name));
            }
            if (Point.IsNotEmpty())
            {
                Point = Point.Trim();
                query.Where(p => p.BusCode.Contains(Point));
            }
            if (Station.IsNotEmpty())
            {
                Station = Station.Trim();
                query.Where(p => p.BusCode.Contains(Station));
            }
            if (startDate.HasValue)
            {
                var startDateDt = startDate.ToDateTime();
                query.Where(p => p.OutTime >= startDateDt);
            }

            if (endDate.HasValue)
            {
                var endDateDt = endDate.ToDateTime().AddDays(1);
                query.Where(p => p.OutTime <= endDateDt);
            }
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="entity">实体</param>
        /// <param name="isCreate">是否新增</param>
        public BoolMessage Save(SyncCarExitStationModel model, bool isCreate)
        {
            SystemReg entity = CreateSystemReg(model);
            entity.Id = StringHelper.Guid();
            reposys.Insert(entity);
            respon.Database.Execute("UPDATE TicketCheckSum SET CheckStatus=1 WHERE Syn_ID=@Syn_ID", new[] { model.Id });
            return BoolMessage.True;
        }


        /// <summary>
        /// 创建系统登记对象
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        private SystemReg CreateSystemReg(SyncCarExitStationModel model)
        {
            SystemReg entity = new SystemReg();
            //ObjectHelper.CopyProperty(model, entity);
            entity.CarNumber = model.BusCode;//车牌号
            if (model.OutTime==null)
            {
                entity.ExitTime = model.CreateTime;//出站时间
            }
            else
            {
                entity.ExitTime = model.OutTime;//出站时间
            }
            entity.RunSecond = model.RunSecond;//运行时间
            entity.Station = model.OwnerStationName;//所属汽车站
            entity.ExitNumber = model.SaleCount;//售票数
            entity.PassengerNumber = model.OutNum;//乘客人数
            entity.SeatCount = model.SeatCount;//核载人数
            entity.DriverName = model.Driver1Name;//驾驶员姓名
            entity.DriverPhone = model.Driver1Tel;//驾驶员电话
            entity.Line = model.LineName;//线路
            entity.Corp = model.OwnerCompName;//运输企业
            entity.ProcessStatus = model.ProcessStatus;//处理结果
            entity.CheckStatus = model.CheckStatus == 1 ? true : false;
            entity.RunStatus = model.RunStatus;
            entity.RegType = model.RegType; ;
            entity.OffenceId = model.OffenceId;
            entity.OffenceName = model.OffenceName;
            entity.Enabled = true;
            entity.Note = model.Note;
            entity.CreateId = model.CreateId;
            entity.CreateName = model.CreateName;
            entity.CreateTime = model.CreateTime;
            entity.PointId = model.PoinID;
            entity.PointName = model.PointName;
            entity.StationID = model.OwnerStation;
            return entity;
        }


        /// <summary>
        /// 获取启用的对象集合(导出)
        /// </summary>
        /// <param name="name">车牌号</param>
        /// <param name="Point">检查点</param>
        /// <param name="Station">所属客运站</param>
        /// <param name="startDate">起始日期</param>
        /// <param name="endDate">截止日期</param>
        /// <returns></returns>
        public List<SyncCarExitStation> GetSyncCarExitStationList(string name, string Point, string Station, string Time)
        {
            var sql = @"SELECT ID,BusCode, OutTime,OwnerStation ,OwnerStationName,SaleCount,SeatCount, OutNum, Driver1Name, Driver1Tel,
                            LineName,OwnerCompName,RunStatus,Syn_Time,CheckStatus,PointName,PointId,DATEDIFF(MI,OutTime,GETDATE()) AS RunSecond
                        FROM  V_ToCheckData 
                        WHERE CheckStatus=0";
            string str_where = " ";
            DateTime time = System.DateTime.Now;
            if (name.IsNotEmpty() && !ValidateHelper.IsStringRegExMatch(name, true, "[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]"))
            {
                str_where += " AND BusCode LIKE '%" + name + "%'";
            }
            if (Point.IsNotEmpty())
            {
                str_where += " AND PointId=@PointId";
            }
            if (Station.IsNotEmpty())
            {
                str_where += " AND OwnerStation=@OwnerStation";
            }
            if (Time.IsNotEmpty())
            {
                time = System.DateTime.Now.AddMinutes(-Convert.ToDouble(Time));
                str_where = " AND OutTime>@Time";
            }
            str_where += " ORDER BY OutTime ASC";
            return repos.Database.Query<SyncCarExitStation>(sql + str_where, new { PointId = Point, OwnerStation = Station, Time = time }).ToList();
        }

        /// <summary>
        /// 获取启用的对象集合(已排序)
        /// </summary>
        /// <param name="name">名称关键字</param>
        /// <param name="enabled">有限</param>
        /// <param name="pointId">检查点</param>
        public List<SyncCarExitStation> GetList(string name, string Point, string Station, string Time)
        {
            var query = repos.SQL.OrderByDesc(p => p.Syn_Time);
            SetQuery(query, name, Point, Station, Time);
            return repos.Fetch(query);
        }

        /// <summary>
        /// 获取对象分页集合
        /// </summary>
        /// <param name="pageCondition">分页对象</param>
        /// <param name="name">名称关键字</param>
        /// <param name="enabled">有限</param>
        /// <param name="pointId">检查点</param>
        public PageList<SyncCarExitStation> GetPageList(PaginationCondition pageCondition,
            string name, string Point, string Station, string Time)
        {
            var sql = @"SELECT ID,BusCode, OutTime,OwnerStation ,OwnerStationName,SaleCount,SeatCount, OutNum, Driver1Name, Driver1Tel,
                            LineName,OwnerCompName,RunStatus,Syn_Time,CheckStatus,PointName,PointId,DATEDIFF(MI,OutTime,GETDATE()) AS RunSecond
                        FROM  V_ToCheckData 
                        WHERE CheckStatus=0";
            string str_where = " ";
            DateTime time = System.DateTime.Now;
            if (name.IsNotEmpty() && !ValidateHelper.IsStringRegExMatch(name, true, "[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]"))
            {
                str_where += " AND BusCode LIKE '%" + name + "%'";
            }
            if (Point.IsNotEmpty())
            {
                str_where += " AND PointId=@PointId";
            }
            if (Station.IsNotEmpty())
            {
                str_where += " AND OwnerStation=@OwnerStation";
            }
            if (Time.IsNotEmpty())
            {
                time = System.DateTime.Now.AddMinutes(-Convert.ToDouble(Time));
                str_where += " AND OutTime>@Time";
            }
            if (pageCondition.OrderName.IsNotEmpty())
            {
                str_where += " ORDER BY " + pageCondition.OrderName + " " + pageCondition.OrderDir;
            }
            else
            {
                str_where += " ORDER BY OutTime ASC";
            }
            var query = repos.Page(pageCondition.PageIndex, pageCondition.PageSize, sql + str_where, new { PointId = Point, OwnerStation = Station, Time = time });
            return query;
        }

        public void SetQuery(SQLQuery<SyncCarExitStation> query, string name, string Point, string Station, string Time)
        {
            if (name.IsNotEmpty())
            {
                name = name.Trim();
                query.Where(p => p.BusCode.Contains(name));
            }
            if (Point.IsNotEmpty())
            {
                Point = Point.Trim();
                query.Where(p => p.BusCode.Contains(Point));
            }
            if (Station.IsNotEmpty())
            {
                Station = Station.Trim();
                query.Where(p => p.BusCode.Contains(Station));
            }
            if (Time.IsNotEmpty())
            {
                DateTime time = System.DateTime.Now.AddMinutes(-Convert.ToDouble(Time));
                query.Where(p => p.OutTime >= time);
            }
        }

        #region 手机端使用的方法
        /// <summary>
        /// 获取启用的对象集合(已排序)
        /// </summary>
        /// <param name="name">名称关键字</param>
        /// <param name="enabled">有限</param>
        /// <param name="pointId">检查点</param>
        public List<SyncCarExitStationModel> GetList(string CarNO, string IsSearchNoPointCar)//DateTime? startDate,
        {
            var sql = @"SELECT ID,BusCode, OutTime,OwnerStation ,OwnerStationName,SaleCount,SeatCount, OutNum, Driver1Name, Driver1Tel,
                            LineName,OwnerCompName,RunStatus,Syn_Time,CheckStatus,PointName,PointId,DATEDIFF(MI,OutTime,GETDATE()) AS RunSecond
                        FROM  V_ToCheckData 
                        WHERE CheckStatus=0";
            string str_where = " ";
            if (IsSearchNoPointCar == "0")
            {
                str_where += " AND PointName IS NOT NULL";
            }
            if (CarNO.IsNotEmpty() && !ValidateHelper.IsStringRegExMatch(CarNO, true, "[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]"))
            {
                str_where += " AND BusCode LIKE '%" + CarNO + "%'";
            }
            else
            {
                str_where += " AND BusCode is null";
            }//BusCode ='陕H07842' and clasdate='2017-11-05'

            //if (startDate.HasValue)
            //{
            //    startDate = startDate.ToDateTime();
            //    str_where += " AND OutTime > @StartDate ";
            //}
            //else
            //{
            //    str_where += " AND OutTime is null ";
            //}
            return repos.Database.Query<SyncCarExitStationModel>(sql + str_where).ToList();
        }

        /// <summary>
        /// 查询条件
        /// </summary>
        /// <param name="query"></param>
        /// <param name="CarNO">车牌号</param>
        public void SetQuery(SQLQuery<SyncCarExitStationModel> query, string CarNO, DateTime? startDate)
        {
            //车牌号
            if (CarNO.IsNotEmpty())
            {
                CarNO = CarNO.Trim();
                query.Where(p => p.BusCode.Contains(CarNO));
            }
            if (startDate.HasValue)
            {
                startDate = startDate.ToDateTime();
                query.Where(p => p.OutTime > startDate);
            }
        }

        /// <summary>
        /// 保存(稽查确认)
        /// </summary>
        /// <param name="ID">实体ID</param>
        public BoolMessage SaveConfrim(SyncCarExitStationModel model)
        {
            if (model != null)
            {
                SystemReg entity = CreateSystemReg(model);
                entity.Id = StringHelper.Guid();
                reposys.Insert(entity);
                respon.Database.Execute("UPDATE TicketCheckSum SET CheckStatus=1 WHERE Syn_ID=@Syn_ID", new[] { model.Id });
                return BoolMessage.True;
            }
            else
            {
                return BoolMessage.False;
            }
        }


        /// <summary>
        /// 保存(违规登记)
        /// </summary>
        /// <param name="ID">实体ID</param>
        public BoolMessage SaveInfraction(SyncCarExitStationModel model)
        {
            if (model != null)
            {
                SystemReg entity = CreateSystemReg(model);
                entity.Id = StringHelper.Guid();
                reposys.Insert(entity);
                respon.Database.Execute("UPDATE TicketCheckSum SET CheckStatus=1 WHERE Syn_ID=@Syn_ID", new[] { model.Id });
                return BoolMessage.True;
            }
            else
            {
                return BoolMessage.False;
            }
        }
        #endregion
    }
}
